package jxt.elt.mail;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;

import jxt.elt.common.DbPool;

public class TransferExpiredJFQ {

	Connection connection;

	public TransferExpiredJFQ() throws SQLException {
		connection = DbPool.getInstance().getConnection();
		connection.setAutoCommit(false);
	}

	// 福利券过期---库存返还、福利券下架。
	public void staffJfq(Date runningDate) {

		try {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(runningDate);
			calendar.set(Calendar.HOUR_OF_DAY, 12);
			calendar.set(Calendar.MINUTE, 0);
			calendar.set(Calendar.SECOND, 0);
			calendar.set(Calendar.MILLISECOND, 0);
			// 任务执行时间分界时间
 			Date demarcationDate = calendar.getTime();
 			String expiredSql = "";
 			String xiajiaSql = "";

			if (runningDate.after(demarcationDate)) { // 假定超过中午12点就为当天晚上执行的任务
				xiajiaSql = "update tbl_jfq set zt=0 where zt <> 0 and yxq <= CURRENT_DATE()";
				expiredSql = "select mc.nid,mc.qy,mc.qyyg,mc.ffly,mc.ffyy,mc.jfq,jfq.jf,jfq.mc from tbl_jfqmc mc,tbl_jfq jfq where mc.jfq=jfq.nid and mc.zt=0 and mc.qy=0 and mc.yxq <= curdate()";
			} else { // 任务执行时间为第二天凌晨
				xiajiaSql = "update tbl_jfq set zt=0 where zt <> 0 and yxq < CURRENT_DATE()";
				expiredSql = "select mc.nid,mc.qy,mc.qyyg,mc.ffly,mc.ffyy,mc.jfq,jfq.jf,jfq.mc from tbl_jfqmc mc,tbl_jfq jfq where mc.jfq=jfq.nid and mc.zt=0 and mc.qy=0 and mc.yxq < curdate()";
			}
 			
			// yxq , ffly->发放来源 ffyy->发放缘由
			// "select mc.nid,mc.qy,mc.qyyg,mc.ffly,mc.ffyy,mc.jfq,jfq.jf,jfq.mc from tbl_jfqmc mc,tbl_jfq jfq where mc.jfq=jfq.nid and mc.qy<>0 and mc.zt=0 and mc.ffzt=1 and (mc.yxq>((curdate() +INTERVAL 1 day) -INTERVAL 30 MINUTE) or (to_days(curdate())-to_days(mc.yxq))>=1) and mc.yxq<?"
			PreparedStatement statement = connection
					.prepareStatement(expiredSql);
			PreparedStatement xiajiaStatement = connection.prepareStatement(xiajiaSql);
			xiajiaStatement.executeUpdate();


//			statement.setDate(1, new java.sql.Date(dateflag.getTime()));

			ResultSet rs = statement.executeQuery();

			PreparedStatement updatest = connection
					.prepareStatement("update tbl_jfqmc set zt=7 where nid =?");

			PreparedStatement queryAccount = connection
					.prepareStatement("select nid,jf from tbl_qyyg where nid =?");

			PreparedStatement createRebates = this.connection
					.prepareStatement("insert into tbl_jfqfdmc (qyyg,jf,jfq,ffsj) values (?,?,?,now())");

			while (rs.next()) {

				//System.out.println("###staffJfq nid :" + rs.getInt("nid"));

				// update disable
				updatest.setInt(1, rs.getInt("nid"));

				int jfqnid = rs.getInt("jfq");
				Statement stmt = connection.createStatement();
				String strsql="update tbl_sp set kcsl=kcsl+1,wcdsl=wcdsl+1 where nid in (select sp from tbl_jfqspref where jfq="+jfqnid+")";
				stmt.executeUpdate(strsql);
				
				//修改积分券库存  XXX 以发放给员工的积分券过期不更新积分券的库存
				//				strsql="update tbl_jfq set kcsl=kcsl-1 where nid="+jfqnid;
				//				stmt.executeUpdate(strsql);
				//　库存回收结束
				
				//
				
				// 福利券过期将积分给员工，后来需求有变，这一步没做
				// int jf = 0;
				if (rs.getInt("qy") != 0 && rs.getInt("qyyg") != 0) {
					// queryAccount.setInt(1, rs.getInt("qyyg"));
					// *****************
					// ResultSet accrs = queryAccount.executeQuery();
					// while (accrs.next()) {
					// jf = accrs.getInt("jf");
					// //System.out.println("###staffJfq jf :" + jf);
					// }
					
					createRebates.setInt(1, rs.getInt("qyyg"));
					createRebates.setInt(2, rs.getInt("jf"));
					createRebates.setInt(3, rs.getInt("jfq"));
					createRebates.execute();
				}

				updatest.executeUpdate();

			}

		} catch (SQLException e) {
			e.printStackTrace();
			System.err
					.println("Runing YG jfq expire notification error,That error msg is: "
							+ e.getMessage());
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				connection = null;
			}
		}
	}

	public void qyjfq(Date runningDate) {

		//System.out.println("Entrying qyjfq!");
		try {
			PreparedStatement statement = connection
					.prepareStatement("select mc.nid from tbl_jfqddmc as mc,tbl_jfq as jfq WHERE mc.jfq=jfq.nid and mc.sl>mc.ffsl and mc.zt=1 and (jfq.yxq>((curdate() +INTERVAL 1 day) -INTERVAL 30 MINUTE) or (to_days(curdate())-to_days(jfq.yxq))>=1) and jfq.yxq<?");

			PreparedStatement updateStatement = connection
					.prepareStatement("update tbl_jfqddmc set zt=7 where nid=?");

			Calendar calendar = Calendar.getInstance();
			calendar.setTime(runningDate);
			calendar.set(Calendar.HOUR_OF_DAY, 0);
			calendar.set(Calendar.MINUTE, 0);
			calendar.set(Calendar.SECOND, 0);
			calendar.set(Calendar.MILLISECOND, 0);

			calendar.add(Calendar.DAY_OF_MONTH, 1);
			Date flagDate = calendar.getTime();
			statement.setDate(1, new java.sql.Date(flagDate.getTime()));

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				//System.out.println("### tbl_jfqddmc nid: " + rs.getInt("nid"));
				updateStatement.setInt(1, rs.getInt("nid"));

				updateStatement.executeUpdate();
			}
			//System.out.println("###End QY expired jfq");
		} catch (SQLException e) {
			e.printStackTrace();
			System.err.println("Runing qyjfq expire error,That error msg is: "
					+ e.getMessage());
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				connection = null;
			}
		}
	}
}
